﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XeBus.BusinessObject;

namespace XeBus.DataAccess
{
    public class ConDuong_LoTrinhDAO
    {
        private ConnectData _conn;
        public ConDuong_LoTrinhDAO()
        {
            _conn = new ConnectData();
        }

        // Hàm thêm mới một lộ trình
        public void THEM_CON_DUONG_LO_TRINH(ConDuong_LoTrinh cl)
        {

            SqlParameter[] param = new SqlParameter[3];

            param[0] = new SqlParameter("P_MA_LO_TRINH", SqlDbType.Int);
            if (cl.Ma_Lo_Trinh.Length > 0)
                param[0].Value = cl.Ma_Lo_Trinh;
            else
                param[0].Value = DBNull.Value;

            param[1] = new SqlParameter("P_MA_CON_DUONG", SqlDbType.Int);
            if (cl.Ma_Con_Duong.Length > 0)
                param[1].Value = cl.Ma_Con_Duong;
            else
                param[1].Value = DBNull.Value;

            param[2] = new SqlParameter("P_SO_THU_TU", SqlDbType.Int);
            if (cl.So_Thu_Tu.Length > 0)
                param[2].Value = cl.So_Thu_Tu;
            else
                param[2].Value = DBNull.Value;

            _conn.ExecuteNonQuery("THEM_CONDUONG_LOTRINH", param);

        }

        public DataTable ConDuong_LoTrinh()
        {
            return _conn.ExecuteSelectQuery("SELECT CL.*,L.Ten_Lo_Trinh,C.Ten_Duong FROM ConDuong_LoTrinh CL INNER JOIN LoTrinh L ON L.Ma_Lo_Trinh=CL.Ma_Lo_Trinh INNER JOIN ConDuong C ON C.Ma_Con_Duong=CL.Ma_Con_Duong;", null);
        }

        public DataTable ConDuong_LoTrinhTheoID(string id)
        {
            return _conn.ExecuteSelectQuery("SELECT CL.*,L.Ten_Lo_Trinh,C.Ten_Duong FROM ConDuong_LoTrinh CL INNER JOIN LoTrinh L ON L.Ma_Lo_Trinh=CL.Ma_Lo_Trinh INNER JOIN ConDuong C ON C.Ma_Con_Duong=CL.Ma_Con_Duong WHERE ID=" + id + "", null);
        }

        public void SUA_CON_DUONG_LO_TRINH(ConDuong_LoTrinh cl)
        {
            SqlParameter[] param = new SqlParameter[4];

            param[0] = new SqlParameter("P_MA_LO_TRINH", SqlDbType.Int);
            if (cl.Ma_Lo_Trinh.Length > 0)
                param[0].Value = cl.Ma_Lo_Trinh;
            else
                param[0].Value = DBNull.Value;

            param[1] = new SqlParameter("P_MA_CON_DUONG", SqlDbType.Int);
            if (cl.Ma_Con_Duong.Length > 0)
                param[1].Value = cl.Ma_Con_Duong;
            else
                param[1].Value = DBNull.Value;

            param[2] = new SqlParameter("P_SO_THU_TU", SqlDbType.Int);
            if (cl.So_Thu_Tu.Length > 0)
                param[2].Value = cl.So_Thu_Tu;
            else
                param[2].Value = DBNull.Value;

            param[3] = new SqlParameter("P_ID", SqlDbType.Int);
            param[3].Value = cl.ID;
            

            _conn.ExecuteNonQuery("SUA_CONDUONG_LOTRINH", param);
        }

        public void XOA(string id)
        {
            _conn.ExecuteNonQueryString("DELETE FROM ConDuong_LoTrinh WHERE ID=" + id + ";");
        }

        public DataTable Ma_Lo_Trinh()
        {
            return _conn.ExecuteSelectQuery("SELECT * FROM LoTrinh",null);
        }

        public DataTable Ma_Con_Duong()
        {
            return _conn.ExecuteSelectQuery("SELECT * FROM ConDuong", null);
        }
    }
}
